import 'package:applications/module/home/po/application_po.dart';
import 'package:sqflite/sqflite.dart';

class ApplicationDao {
  final Database db;

  ApplicationDao(this.db);

  static Future<void> create(Database db) async {
    // 新建表方法
    String createSql = //插入数据
        "CREATE TABLE application ("
        "id INTEGER PRIMARY KEY AUTOINCREMENT,"
        "name TEXT,"
        "image TEXT,"
        "category TEXT,"
        "averageUserRating REAL,"
        "userRatingCount INTEGER,"
        "description TEXT,"
        "artistName TEXT,"
        "sort INTEGER"
        ")";
    return db.execute(createSql);
  }

  static Future<void> onUpgrade(Database db, int oldVersion) async {
    if (oldVersion < 2) {
      await db.execute(
        "ALTER TABLE application ADD COLUMN description TEXT",
      );
      await db.execute(
        "ALTER TABLE application ADD COLUMN artistName TEXT",
      );
    }
  }

  /// 批量插入方法
  Future<int> inserts(List<ApplicationPo> list) async {
    // //开始批量
    // Batch batch = db.batch();
    // //删除
    // batch.delete('application');
    // for (var po in list) {
    //   //插入
    //   batch.insert('application', po.toJson());
    // }
    // //执行批量
    // return await batch.commit();

    if (list.isEmpty) return Future.value(0);
    //插入数据
    StringBuffer addSql = StringBuffer("INSERT INTO "
        "application(id,name,image,category,averageUserRating, userRatingCount,sort)  "
        "VALUES");
    for (int i = 0; i < list.length; i++) {
      var po = list[i];
      addSql.write("('");
      addSql.writeAll([
        "${po.id}",
        po.name.replaceAll("'", "''"),
        po.image.replaceAll("'", "''"),
        po.category.replaceAll("'", "''"),
        po.averageUserRating,
        po.userRatingCount,
        i,
      ], "','");
      if (i == list.length - 1) {
        addSql.write("');");
      } else {
        addSql.write("'),");
      }
    }
    return await db.transaction((tran) {
      tran.delete("application");
      return tran.rawInsert(addSql.toString());
    });
  }

  /// 插入方法
  Future<int> insert(ApplicationPo po) async {
    return db.transaction(
        (tran) async => await tran.insert("application", po.toJson()));
  }

  Future<int> update(Map<String, Object?> values, int id) async {
    return await db.transaction((tran) async => await tran
        .update('application', values, where: 'id = ?', whereArgs: [id]));
  }

  Future<List<ApplicationPo>> queryAll() async {
    List<Map<String, dynamic>> list =
        await db.rawQuery("SELECT * FROM application order by sort asc;");
    if (list.isEmpty) return [];
    return list.map((bean) => ApplicationPo.fromJson(bean)).toList();
  }

  Future<List<ApplicationPo>> queryPagesByName(
    int limit,
    int offset, {
    String search = '',
  }) async {
    List<Map<String, dynamic>> list = await db.query(
      "application",
      limit: limit,
      offset: offset,
      where: "name LIKE ? or description LIKE ? or artistName LIKE ?",
      whereArgs: [
        "%$search%",
        "%$search%",
        "%$search%",
      ],
      orderBy: 'sort asc',
    );
    if (list.isEmpty) return [];
    return list.map((bean) => ApplicationPo.fromJson(bean)).toList();
  }
}
